﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace centraldba_library.Data
{
    public class RecoveryManagerEventArgs : EventArgs
    {
        public string Message { get; set; }
        public RecoveryManagerEventArgs(string message)
        {
            Message = message;
        }
    }

    public enum BackupType
    {
        Full,
        Differential,
        Log,
        Snapshot
    }

    public class recovery_manager
    {
        #region " Fields "

        private string master_connection_string, master_server_name, master_database_name;
        SqlInfoMessageEventHandler sqlMessageHandler;

        public event SqlInfoMessageEventHandler InfoMessage;
        public event EventHandler ProcessComplete;
        public event EventHandler Error;

        #endregion

        #region " Constructor "

        public recovery_manager(string connection_string, string server_name, string database_name)
        {
            master_connection_string = connection_string;
            master_server_name = server_name;
            master_database_name = database_name;

            // Validate a server name that isn't "yourserver"
            if (master_server_name.Equals("yourserver"))
                throw new Exception("The master_server_name has not been set.");

            // Validate Connection String template is in order
            bool validString = true;

            if (!master_connection_string.Contains("Data Source=yourserver"))
                validString = false;

            if (!master_connection_string.Contains("Initial Catalog=centraldba_db"))
                validString = false;

            if (!validString)
                throw new Exception("The setting centraldba_db_template has been tampered with.  Please do not change [Data Source=yourserver] or [Initial Catalog=centraldba_db].");
        }
        public recovery_manager(string connection_string, string server_name, string database_name, SqlInfoMessageEventHandler eventHandler)
        {
            master_connection_string = connection_string;
            master_server_name = server_name;
            master_database_name = database_name;
            sqlMessageHandler = eventHandler;

            // Validate a server name that isn't "yourserver"
            if (master_server_name.Equals("yourserver"))
                throw new Exception("The master_server_name has not been set.");

            // Validate Connection String template is in order
            bool validString = true;

            if (!master_connection_string.Contains("Data Source=yourserver"))
                validString = false;

            if (!master_connection_string.Contains("Initial Catalog=centraldba_db"))
                validString = false;

            if (!validString)
                throw new Exception("The setting centraldba_db_template has been tampered with.  Please do not change [Data Source=yourserver] or [Initial Catalog=centraldba_db].");
        }

        #endregion

        #region " Events "

        protected virtual void OnInfoMessage(SqlInfoMessageEventArgs e)
        {
            if (InfoMessage != null)
            {
                InfoMessage(this, e);
            }
        }

        protected virtual void OnError(RecoveryManagerEventArgs e)
        {
            if (Error != null)
            {
                Error(this, e);
            }
        }

        #endregion

        #region " Database Objects "

        public async Task<bool> ExecuteQuery(string serverName, string cmdQuery)
        {
            // Trim Whitespace
            serverName = serverName.Trim();
            cmdQuery = cmdQuery.Trim();

            // Enforce parameter values as mandatory
            if (serverName.Length.Equals(0) || cmdQuery.Length.Equals(0))
            {
                return false;
                throw new Exception("Missing parameter (serverName and cmdQuery are both mandatory)");
            }
            else
            {
                try
                {
                    SqlInfoMessageEventHandler errHandler = delegate(object sender, SqlInfoMessageEventArgs e)
                    {
                        OnInfoMessage(e);
                    };
                    SqlCommand cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name) + ";Asynchronous Processing=True;", cmdQuery, System.Data.CommandType.Text);
                    cm.Connection.FireInfoMessageEventOnUserErrors = false;
                    cm.Connection.InfoMessage += errHandler;
                    cm.CommandTimeout = 0;
                    int rowsAffected = await cm.ExecuteNonQueryAsync();

                    return true;
                }
                catch (Exception ex) {
                    OnError(new RecoveryManagerEventArgs(ex.Message));
                    return false;
                }
            }
        }

        public ObservableCollection<backup> GetAllBackups(string serverName, string databaseName, string stopat, string backupLabel, bool fileExists, bool recoveryWindow)
        {
            ObservableCollection<backup> results = null;

            if (!serverName.Length.Equals(0))
            {
                try
                {
                    // Declare Variables
                    object start_date;
                    object label;

                    // Parse/validate parameters
                    if (stopat.Trim().Length.Equals(0))
                        start_date = null;
                    else
                        start_date = stopat.Trim();

                    if (backupLabel.Trim().Length.Equals(0))
                        label = null;
                    else
                        label = backupLabel.Trim();


                    // Get resultset
                    SqlCommand cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_GetAllBackups]", System.Data.CommandType.StoredProcedure);
                    cm.CommandTimeout = 0;
                    cm.Parameters.AddWithValue("database_name", databaseName);
                    cm.Parameters.AddWithValue("start_date", start_date);
                    cm.Parameters.AddWithValue("label", label);
                    cm.Parameters.AddWithValue("files_must_exist", fileExists);
                    cm.Parameters.AddWithValue("recovery_window", recoveryWindow);
                    SqlDataReader sdr = cm.ExecuteReader();
                    while (sdr.Read())
                    {
                        if (results == null)
                            results = new ObservableCollection<backup>();

                        backup bkp = new backup();
                        bkp.id = sdr.GetInt64(0);
                        if (sdr.GetValue(1) == null || sdr.GetValue(1) == System.DBNull.Value)
                            bkp.recovery_path_broken = false;
                        else
                            bkp.recovery_path_broken = sdr.GetBoolean(1);
                        bkp.backup_set_id = sdr.GetInt32(2);
                        bkp.database_name = sdr.GetValue(5).ToString();
                        bkp.name = sdr.GetValue(6).ToString();
                        bkp.type = sdr.GetValue(7).ToString();
                        bkp.backup_start_date = sdr.GetDateTime(8);
                        bkp.has_bulk_logged_data = sdr.GetBoolean(9);
                        bkp.backup_size = sdr.GetDecimal(10);
                        bkp.physical_device_name = sdr.GetValue(11).ToString();
                        bkp.file_exists = sdr.GetBoolean(12);

                        results.Add(bkp);
                    }
                    SqlHelper.CloseConnection(cm, sdr);
                }
                catch (Exception ex) { throw ex; }
            }

            // Return a CollectionViewSource with default sort
            return results;
        }

        public ObservableCollection<restore_script> GetRestoreScripts(string sourceInstance, string sourceDatabase, string destinationDatabase, string restoreLabel, Int32 backup_set_id, string stopat_time)
        {
            // Declare Local Variables
            ObservableCollection<restore_script> results = null;
            DateTime d_stopat_time;

            // Pre-process parameters
            sourceInstance = sourceInstance.Trim();
            sourceDatabase = sourceDatabase.Trim();
            destinationDatabase = destinationDatabase.Trim();
            stopat_time = stopat_time.Trim();

            // Determine the d_stopat_time value
            if (!DateTime.TryParse(stopat_time, out d_stopat_time))
                d_stopat_time = DateTime.Now;

            // Get the result set
            if (!sourceInstance.Length.Equals(0))
            {
                try
                {
                    SqlCommand cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sourceInstance, master_database_name), "[dbo].[spRest_GetRestoreScripts]", System.Data.CommandType.StoredProcedure);
                    cm.CommandTimeout = 0;
                    cm.Parameters.AddWithValue("database_name", sourceDatabase);
                    cm.Parameters.AddWithValue("last_backup_set_id", backup_set_id);
                    cm.Parameters.AddWithValue("destination_db", destinationDatabase.ToString());
                    cm.Parameters.AddWithValue("stopat_time", d_stopat_time);
                    SqlDataReader sdr = cm.ExecuteReader();
                    while (sdr.Read())
                    {
                        if (results == null)
                            results = new ObservableCollection<restore_script>();

                        restore_script scr = new restore_script();
                        scr.Name = sdr.GetValue(1).ToString();
                        scr.Command = sdr.GetValue(2).ToString();
                        scr.Result = sdr.GetValue(4).ToString();
                        scr.Started = sdr.GetBoolean(5);
                        scr.Succeeded = sdr.GetBoolean(6);
                        scr.Processing = sdr.GetBoolean(7);

                        results.Add(scr);
                    }
                    SqlHelper.CloseConnection(cm, sdr);
                }
                catch (Exception ex) { throw ex; }
            }

            return results;
        }
        
        #endregion

        #region " Methods "

        public void BackupDatabase(string serverName, string databaseName, BackupType backupType)
        {
            BackupDatabase(serverName, databaseName, backupType, String.Empty);
        }
        public void BackupDatabase(string serverName, string databaseName, BackupType backupType, string backupLabel)
        {
            // Declare Local Variables
            SqlCommand cm = null;

            // Parameter Preprocessing
            if (backupLabel == null) backupLabel = String.Empty;

            try
            {
                // Get the proper SqlCommand
                switch (backupType)
                {
                    case BackupType.Full:
                        cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_Full]", System.Data.CommandType.StoredProcedure);
                        break;

                    case BackupType.Differential:
                        cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_Differential]", System.Data.CommandType.StoredProcedure);
                        break;

                    case BackupType.Snapshot:
                        cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_SnapshotCreate]", System.Data.CommandType.StoredProcedure);
                        break;

                    default:
                        cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_Log]", System.Data.CommandType.StoredProcedure);
                        break;
                }
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.CommandTimeout = 0;

                // Add parameters
                cm.Parameters.AddWithValue("database_name", databaseName);
                if (!backupLabel.Equals(String.Empty)) cm.Parameters.AddWithValue("label", backupLabel);

                // Execute the Command
                cm.ExecuteNonQuery();
            }
            catch (Exception ex) 
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "/" + databaseName + "] BackupDatabase ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm);
            }
        }

        public void DropSnapshot(string serverName, string databaseName, string backupLabel)
        {
            // Declare Local Variables
            SqlCommand cm = null;

            try
            {
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_SnapshotDrop]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("database_name", databaseName);
                cm.Parameters.AddWithValue("label", backupLabel);
                cm.ExecuteNonQuery();
            }
            catch (Exception ex) 
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "/" + databaseName + "] DropSnapshot ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm);
            }
        }

        public void RestoreDatabase(string serverName, string databaseName, string backupLabel, string snapshotLabel, object stop_at_datetime, bool skipPreBackup)
        {
            // Declare Local Variables
            SqlCommand cm = null;
            bool snapExists = false;

            // Parameter Preprocessing
            if (backupLabel == null) backupLabel = String.Empty;
            if (snapshotLabel == null) snapshotLabel = String.Empty;
            if (stop_at_datetime == null) stop_at_datetime = String.Empty;

            try
            {
                // Is there a snapshot to restore from?
                if (!snapshotLabel.Equals(String.Empty))
                {
                    cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spBkp_SnapshotExists]", System.Data.CommandType.StoredProcedure);
                    if (sqlMessageHandler != null)
                        cm.Connection.InfoMessage += sqlMessageHandler;
                    cm.CommandTimeout = 0;
                    cm.Parameters.AddWithValue("database_name", databaseName);
                    cm.Parameters.AddWithValue("label", snapshotLabel);
                    cm.Parameters.Add("return_value", SqlDbType.Int);
                    cm.Parameters["return_value"].Direction = ParameterDirection.ReturnValue;
                    cm.ExecuteNonQuery();
                    snapExists = !cm.Parameters["return_value"].Value.Equals(0);
                    cm.Dispose();
                }

                // If there is a snapshot, restore the snapshot
                if (snapExists)
                {
                    cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_Snapshot]", System.Data.CommandType.StoredProcedure);
                    cm.Parameters.AddWithValue("label", snapshotLabel);
                }
                else if (!backupLabel.Equals(String.Empty))
                {
                    cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_ByLabel]", System.Data.CommandType.StoredProcedure);
                    cm.Parameters.AddWithValue("restore_label", backupLabel);
                    cm.Parameters.AddWithValue("restore_now", 1);
                    cm.Parameters.AddWithValue("verify_only", 0);
                    if (!stop_at_datetime.Equals(String.Empty))
                        cm.Parameters.AddWithValue("stopat_time", stop_at_datetime);
                }
                else
                {
                    cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_ByPointInTime]", System.Data.CommandType.StoredProcedure);
                    if (!stop_at_datetime.Equals(String.Empty))
                        cm.Parameters.AddWithValue("stopat_time", stop_at_datetime);
                    cm.Parameters.AddWithValue("restore_now", 1);
                    cm.Parameters.AddWithValue("verify_only", 0);
                }

                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("database_name", databaseName);
                cm.Parameters.AddWithValue("skip_pre_backup", skipPreBackup);
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "/" + databaseName + "] RestoreDatabase ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm);
            }
        }

        public void CopyDatabase(string serverName, string databaseName, string destinationServerName, string destinationDatabaseName, string backupLabel, bool skipPreBackup)
        {
            // Declare Local Variables
            SqlCommand cm = null;
            SqlCommand cmDestination = null;
            SqlDataReader sdr = null;
            Int32 last_backup_set_id = 0;

            // Parameter Preprocessing
            if (backupLabel == null) backupLabel = String.Empty;
            if (destinationDatabaseName == null || destinationDatabaseName.ToString().Equals(String.Empty)) 
                destinationDatabaseName = databaseName;

            try
            {
                // Determine the ID of the last backup
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_DetermineLastBackup]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                cm.Parameters.AddWithValue("database_name", databaseName);
                if (!backupLabel.Equals(String.Empty))
                {
                    cm.Parameters.AddWithValue("restore_label", backupLabel);
                }
                last_backup_set_id = (Int32)cm.ExecuteScalar();
                SqlHelper.CloseConnection(cm);
                
                // Copy the database
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_ByBackupSetID]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("database_name", databaseName);
                cm.Parameters.AddWithValue("destination_db", destinationDatabaseName);
                cm.Parameters.AddWithValue("last_backup_set_id", last_backup_set_id);
                cm.Parameters.AddWithValue("restore_now", 0);
                cm.Parameters.AddWithValue("verify_only", 0);
                cm.Parameters.AddWithValue("skip_pre_backup", skipPreBackup);

                sdr = cm.ExecuteReader();
                while (sdr.Read())
                {
                    cmDestination = SqlHelper.GetCommand(SqlHelper.GetConnectionString(destinationServerName, master_database_name), sdr["command"].ToString(), System.Data.CommandType.Text);
                    if (sqlMessageHandler != null)
                        cmDestination.Connection.InfoMessage += sqlMessageHandler;
                    cmDestination.ExecuteNonQuery();
                    cmDestination.Dispose();
                }
            }
            catch (Exception ex)
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "/" + databaseName + "] CopyDatabase ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm, sdr);
                SqlHelper.CloseConnection(cmDestination);
            }
        }

        public void LogShipDatabase(string serverName, string databaseName, string destinationServerName, string destinationDatabaseName)
        {
            // Declare Local Variables
            SqlCommand cm = null;
            SqlCommand cmDestination = null;
            SqlDataReader sdr = null;
            Int32 last_backup_set_id = 0;
            String fileName;
            Int32 destination_state = 0;
            Boolean destination_is_standby = false;

            // Parameter Preprocessing
            if (destinationDatabaseName == null || destinationDatabaseName.ToString().Equals(String.Empty))
                destinationDatabaseName = databaseName;

            try
            {
                // Get information on the last file that was restored to the destination server/database
                cmDestination = SqlHelper.GetCommand(SqlHelper.GetConnectionString(destinationServerName, master_database_name), "[dbo].[spRest_GetLastRestoredFile]", System.Data.CommandType.StoredProcedure);
                cmDestination.Parameters.AddWithValue("database_name", destinationDatabaseName);
                sdr = cmDestination.ExecuteReader();

                // No file was restored; reinit
                if (!sdr.Read())
                    fileName = String.Empty;
                else
                {
                    // If the file was not fully restored; reinit (column0 = is_full_restore)
                    if (!sdr.GetBoolean(0))
                        fileName = String.Empty;

                    // If the source server and database do not match; reinit (column1 = machine_name, column2 = database_name)
                    else if (!(sdr.GetString(1).Equals(serverName) && sdr.GetString(2).Equals(databaseName)))
                        fileName = String.Empty;

                    // Retrieve the last restore file information
                    else
                    {
                        fileName = sdr.GetString(3);                // column3 = physical_device_name
                        destination_state = sdr.GetInt32(5);        // column5 = destination_state
                        destination_is_standby = sdr.GetBoolean(6);   // column6 = destination_is_standby
                    }
                }
                SqlHelper.CloseConnection(cmDestination, sdr);

                // Determine the ID of the last backup
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_DetermineLastBackup]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                cm.Parameters.AddWithValue("database_name", databaseName);
                last_backup_set_id = (Int32)cm.ExecuteScalar();
                SqlHelper.CloseConnection(cm);

                // Copy the database
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_GetRestoreScripts]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("database_name", databaseName);
                cm.Parameters.AddWithValue("destination_db", destinationDatabaseName);
                cm.Parameters.AddWithValue("last_backup_set_id", last_backup_set_id);
                cm.Parameters.AddWithValue("skip_pre_backup", 1);
                cm.Parameters.AddWithValue("recover_now", 0);
                cm.Parameters.AddWithValue("last_file_restored", fileName);
                cm.Parameters.AddWithValue("destination_state", destination_state);
                cm.Parameters.AddWithValue("destination_is_standby", destination_is_standby);

                sdr = cm.ExecuteReader();
                while (sdr.Read())
                {
                    cmDestination = SqlHelper.GetCommand(SqlHelper.GetConnectionString(destinationServerName, master_database_name), sdr["command"].ToString(), System.Data.CommandType.Text);
                    if (sqlMessageHandler != null)
                        cmDestination.Connection.InfoMessage += sqlMessageHandler;
                    cmDestination.ExecuteNonQuery();
                    cmDestination.Dispose();
                }
            }
            catch (Exception ex)
            {
                OnError(new RecoveryManagerEventArgs("[" + destinationServerName + "/" + destinationDatabaseName + "] LogShipDatabase ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm, sdr);
                SqlHelper.CloseConnection(cmDestination);
            }
        }

        public string GetLastBackupLabel(string serverName, string databaseName)
        {
            // Declare Local Variables
            string label = String.Empty;
            SqlCommand cm = null;

            try
            {
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spRest_GetLastLabel]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("database_name", databaseName);
                label = cm.ExecuteScalar().ToString();
                if (label == null) label = String.Empty;
            }
            catch (Exception ex)
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "/" + databaseName + "] GetLastBackupLabel ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm);
            }

            // Return the backup label that was found
            return label;
        }

        public void CreateLogEntry(string serverName, string logMessage)
        {
            // Declare Local Variables
            SqlCommand cm = null;

            try
            {
                cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spMaint_InsertLogEntry]", System.Data.CommandType.StoredProcedure);
                cm.CommandTimeout = 0;
                if (sqlMessageHandler != null)
                    cm.Connection.InfoMessage += sqlMessageHandler;
                cm.Parameters.AddWithValue("program_name", String.Empty);
                cm.Parameters.AddWithValue("message", logMessage);
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                OnError(new RecoveryManagerEventArgs("[" + serverName + "] CreateLogEntry ERROR:  " + ex.Message.ToString()));
            }
            finally
            {
                // Close Objects
                SqlHelper.CloseConnection(cm);
            }
        }

        #endregion
    }
}
